package cn.xtits.xtf.common.db;

import cn.xtits.xtf.common.exception.XTDbException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by ShengHaiJiang on 16/4/5.
 */
public class DbHelper {
    private static final Logger logger = LoggerFactory.getLogger(DbHelper.class);
    private static final String QUERY_SEQUENCE_SQL = "SELECT %s.nextval FROM dual";
    private final XTRowProcessor rowProcessor = new XTRowProcessor();


    private QueryRunner getQueryRunner() {
        return new QueryRunner();
    }

    /**
     * 获取Oracle Sequence
     *
     * @param connection
     * @param sequenceName
     * @return
     */
    public long querySequence(Connection connection, String sequenceName) {
        long startTime = System.currentTimeMillis();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = String.format(QUERY_SEQUENCE_SQL, sequenceName);
            ps =  connection.prepareStatement(sql);
            rs = ps.executeQuery();
            long sequence = 0L;
            if(rs.next()) {
                sequence = rs.getLong(1);
            }
            printCostTimeLog(sql, null, (System.currentTimeMillis() - startTime));
            return sequence;
        } catch (SQLException e) {
            logger.error("query sequence error !, e: {}", e);
            throw new XTDbException("query sequence error !");
        } finally {
            DbUtils.closeQuietly(null, ps, rs);
        }
    }

    /**
     * 新增、修改、删除操作时调用此方法
     *
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public int update(Connection connection, String sql, Object[] params) {
        long startTime = System.currentTimeMillis();
        QueryRunner queryRunner = getQueryRunner();
        try {
            int result = queryRunner.update(connection, sql, params);
            printCostTimeLog(sql, params, (System.currentTimeMillis() - startTime));
            return result;
        } catch (SQLException e) {
            logger.error("queryRunner.update error !, e: {}", e);
            throw new XTDbException("queryRunner.update error !");
        }
    }

    /**
     * 把结果集中的第一行转为JavaBean实例返回
     *
     * @param connection
     * @param sql
     * @param param
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> T queryForObject(Connection connection, String sql, Object[] params, Class<T> clazz) {
        long startTime = System.currentTimeMillis();
        QueryRunner queryRunner = getQueryRunner();
        try {
            T obj = queryRunner.query(connection, sql, new BeanHandler<>(clazz, rowProcessor), params);
            printCostTimeLog(sql, params, (System.currentTimeMillis() - startTime));
            return obj;
        } catch (SQLException e) {
            logger.error("queryRunner.query error !, e: {}", e);
            throw new XTDbException("queryRunner.query error !");
        }
    }

    /**
     * 将结果集中的每一行数据都封装到一个对应的JavaBean实例，
     * 存放在List中返回
     *
     * @param connection
     * @param sql
     * @param params
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> List<T> queryForList(Connection connection, String sql, Object[] params, Class<T> clazz) {
        long startTime = System.currentTimeMillis();
        QueryRunner queryRunner = getQueryRunner();
        try {
            List<T> objList = queryRunner.query(connection, sql, new BeanListHandler<>(clazz, rowProcessor), params);
            printCostTimeLog(sql, params, (System.currentTimeMillis() - startTime));
            return objList;
        } catch (SQLException e) {
            logger.error("queryRunner.query error !, e: {}", e);
            throw new XTDbException("queryRunner.query error !");
        }
    }

    /**
     *
     * 统计记录条数、金额等,可以直接调用此方法
     * <p>SELECT count(1)/sum(price) FROM test</p>
     *
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public <T> T queryForStatistics(Connection connection, String sql, Object[] params) {
        long startTime = System.currentTimeMillis();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps =  connection.prepareStatement(sql);
            if(params != null && params.length > 0) {
                setParameter(ps, params);
            }
            rs = ps.executeQuery();
            T result = null;
            if(rs.next())
                result = (T) rs.getObject(1);
            printCostTimeLog(sql, params, (System.currentTimeMillis() - startTime));
            return result;
        } catch (SQLException e) {
            logger.error("query sequence error !, e: {}", e);
            throw new XTDbException("query sequence error !");
        } finally {
            DbUtils.closeQuietly(null, ps, rs);
        }
    }

    private void setParameter(PreparedStatement statement, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
        }
    }

    /**
     * 直接返回ResultSet
     *
     * @param connection
     * @param sql
     * @param params
     * @return
     */
    public ResultSet query(Connection connection, String sql, Object[] params) {
        long startTime = System.currentTimeMillis();
        try {
            PreparedStatement ps =  connection.prepareStatement(sql);
            if(params != null && params.length > 0) {
                setParameter(ps, params);
            }
            ResultSet rs = ps.executeQuery();
            printCostTimeLog(sql, params, (System.currentTimeMillis() - startTime));
            return rs;
        } catch (SQLException e) {
            logger.error("query sequence error !, e: {}", e);
            throw new XTDbException("query sequence error !");
        }
    }

    /**
     * 调用存储过程
     *
     * @param connection
     * @param sql
     * @param params
     */
    public void executeProcedure(Connection connection, String sql, ProcedureParameter[] params) {
        long startTime = System.currentTimeMillis();
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(sql);
            List<Integer> outIndexList = new ArrayList<>();
            for(int i = 0; i < params.length; i++)
            {
                if(params[i].getDirection() == ParameterDirection.OUTPUT) {
                    statement.registerOutParameter(i+1, params[i].getType());
                    outIndexList.add(i);
                } else if(params[i].getDirection() == ParameterDirection.INPUT){
                    statement.setObject(i+1, params[i].getValue());
                }
            }
            statement.execute();
            for(int index: outIndexList) {
                params[index].setValue(statement.getObject(index+1));
            }
            printCostTimeLog(sql, params, (System.currentTimeMillis() - startTime));
        } catch (SQLException e) {
            logger.error("call procedure error !, e: {}", e);
            throw new XTDbException("call procedure error !");
        } finally {
            DbUtils.closeQuietly(statement);
        }
    }

    /**
     * 开启事务
     *
     * @param connection
     */
    public void beginTransaction(Connection connection) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            logger.error("Failed to open transaction!, e: {}", e);
            throw new XTDbException("Failed to open transaction!");
        }
    }

    /**
     * 提交事务
     *
     * @param connection
     */
    public void commit(Connection connection) {
        try {
            if(connection != null) {
                connection.commit();
                connection.setAutoCommit(true);
                logger.info("Successfully commit.");
            }
        } catch (SQLException e) {
            logger.error("commitAndClose error!");
            throw new XTDbException("Failed to commit!");
        }
    }

    /**
     * 回滚事务
     *
     * @param connection
     */
    public void rollback(Connection connection) {
        try {
            DbUtils.rollbackAndClose(connection);
            logger.info("Successfully rollback!");
        } catch (SQLException e) {
            logger.error("rollback error!");
        }
    }

    /**
     * 关闭连接
     *
     * @param connection
     */
    public void close(Connection connection) {
        try {
            DbUtils.close(connection);
        } catch (SQLException e) {
            logger.error("close error!");
        }
    }

    private void printCostTimeLog(String sql, Object[] params, long costTime){
        List<Object> list;
        if(params != null) {
            list = Arrays.asList(params);
        } else {
            list = new ArrayList<>();
        }

        logger.debug("Execute SQL[{}], params{}, cost time[{}]ms.", new Object[]{sql, list, costTime});
    }

}
